﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Runtime.InteropServices;
using System.Diagnostics;

namespace XD.Office
{
    /// <summary>
    /// excel操作辅助类
    /// </summary>
    public class ExcelHelper : IDisposable
    {
        #region 字段

        private object missing = Missing.Value;
        private Application application;
        private Workbook workbook;
        private Worksheet worksheet;
        
        #endregion

        #region 构造函数

        private ExcelHelper() { }

        public ExcelHelper(string excelFile, EnumXlMode mode = EnumXlMode.exOpenOrCreate)
        {
            this.application = new Application();
            this.application.Visible = false;
            this.application.DisplayAlerts = false;
            this.workbook = this.OpenExcel(excelFile, mode);            
        }

        #endregion

        #region 公共方法

        #region 保存

        /// <summary>
        /// 保存文件
        /// </summary>
        public void Save()
        {
            this.workbook.Save();
        }

        /// <summary>
        /// 另存文件
        /// </summary>
        /// <param name="savePath">保存路径</param>
        public void SaveAs(string savePath)
        {
            object fileName = (object)savePath;
            this.workbook.SaveAs(fileName, missing, missing, missing, missing,
                missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
        }

        /// <summary>         
        /// 另存为html文件         
        /// </summary>         
        /// <param name="CurBook">Workbook</param>         
        /// <param name="strFilePath">文件路径</param>         
        public void SaveHtml(string strFilePath)
        {
            this.workbook.SaveAs(strFilePath, XlFileFormat.xlHtml, missing, missing, missing, missing,
                XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        } 

        #endregion

        #region sheet

        /// <summary>
        /// 获取工作表数目
        /// </summary>
        /// <returns></returns>
        public int GetWorkSheetCount()
        {
            return this.workbook.Sheets.Count;
        }

        /// <summary>
        /// 设置当前工作表
        /// </summary>
        /// <param name="index"></param>
        public void SetCurrentWorkSheet(int index)
        {
            this.worksheet = this.workbook.Sheets[index];
        }

        /// <summary>
        /// 设置当前工作表
        /// </summary>
        /// <param name="sheetName">工作表名字</param>
        public void SetCurrentWorkSheet(string sheetName)
        {
            this.worksheet = null;
            foreach (Worksheet sheet in this.workbook.Sheets)
            {
                if (sheet.Name.Contains(sheetName))
                {
                    this.worksheet = sheet;
                    break;
                }
            }
        }

        /// <summary>
        /// 创建sheet
        /// </summary>
        /// <param name="sheetname"></param>
        public void CreateSheet(string sheetname = null)
        {
            this.worksheet = this.workbook.Worksheets.Add(missing, missing, 1, missing);
            if (sheetname != null && sheetname.Trim().Equals(String.Empty) == false)
                this.worksheet.Name = sheetname;
        }

        /// <summary>
        /// 重命名sheet名称
        /// </summary>
        /// <param name="sheetname"></param>
        public void SetWorkSheetName(string sheetname)
        {
            this.worksheet.Name = sheetname;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="width"></param>
        public void SetColumnWidth(double width)
        {
            this.worksheet.Columns.ColumnWidth = width;
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="height"></param>
        public void SetRowHeight(double height)
        {
            this.worksheet.Rows.RowHeight = height;
        }

        /// <summary>
        /// 复制当前sheet到指定的位置
        /// </summary>
        /// <param name="before"></param>
        /// <param name="after"></param>
        public void CopySheet(string before, string after)
        { 
            object beforeSheet = missing;
            if (before != null && before.Trim().Equals(String.Empty) == false)
                beforeSheet = (Worksheet)this.workbook.Worksheets[before];
            object afterSheet = missing;
            if (after != null && after.Trim().Equals(String.Empty) == false)
                afterSheet = (Worksheet)this.workbook.Worksheets[after];
            this.worksheet.Copy(beforeSheet, afterSheet);
        }
        
        /// <summary>
        /// 删除指定的sheet
        /// </summary>
        /// <param name="index"></param>

        public void RemoveSheet(int index)
        {
            if (this.workbook.Sheets.Count >= index)
            {
                this.worksheet = this.workbook.Sheets[index];
                this.worksheet.Delete();
            }
        }

        #endregion

        #region cell

        /// <summary>
        /// 获取指定行的值
        /// </summary>
        /// <param name="rowIndex">行的索引，从1开始</param>
        /// <returns>Dictionary型数据(单元格序号,单元格的值),例如：(A2,23.42)</returns>
        public Dictionary<string, string> GetRowValues(int rowIndex)
        {
            Dictionary<string, string> valuesDic = new Dictionary<string, string>();
            for (int i = 1; i <= this.worksheet.Columns.Count; i++)
            {                
                string column = this.GetAddress(rowIndex, i);
                Range cellRange = (Range)this.worksheet.Cells[rowIndex, i];
                if (cellRange == null)
                    continue;
                object nextCell = cellRange.Value2;
                if (nextCell != null)
                {
                    string cellValue = nextCell.ToString().Trim();
                    valuesDic.Add(column, cellValue);
                }
            }
            return valuesDic;
        }

        /// <summary>
        /// 获取指定单元格的值
        /// </summary>
        /// <param name="columnIndex">列索引，例如:"A"</param>
        /// <param name="rowIndex">行索引，从1开始</param>
        /// <returns>指定单元格的值</returns>
        public string GetCellValue(string columnIndex, int rowIndex)
        {
            Range cellRange = (Range)this.worksheet.Cells[rowIndex, columnIndex];
            if (cellRange == null)
                return null;
            object nextCell = cellRange.Value2;
            if (nextCell == null)
                return null;
            return nextCell.ToString();
        }

        /// <summary>
        /// 获取指定单元格的值
        /// </summary>
        /// <param name="columnIndex">列索引，从1开始</param>
        /// <param name="rowIndex">行索引，从1开始</param>
        /// <returns>指定单元格的值</returns>
        public string GetCellValue(int columnIndex, int rowIndex)
        {
            Range cellRange = (Range)this.worksheet.Cells[rowIndex, columnIndex];
            if (cellRange == null)
                return null;
            object nextCell = cellRange.Value2;
            if (nextCell == null)
                return null;
            return nextCell.ToString();
        }

        /// <summary>
        /// 向指定的单元格中填充值
        /// </summary>
        /// <param name="columnIndex">列索引，从1开始</param>
        /// <param name="rowIndex">行索引，从1开始</param>
        /// <param name="value">要填充的值</param>
        public void SetCellValue(int columnIndex, int rowIndex, string value)
        {
            this.worksheet.Cells[rowIndex, columnIndex] = value;
        }

        /// <summary>
        /// 向指定的单元格中填充值
        /// </summary>
        /// <param name="columnIndex">列索引,例如："A"</param>
        /// <param name="rowIndex">行索引，从1开始</param>
        /// <param name="value">要填充的值</param>
        public void SetCellValue(string columnIndex, int rowIndex, string value)
        {
            this.worksheet.Cells[rowIndex, columnIndex] = value;
        }

        /// <summary>
        /// 设置单元格的列宽
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="width"></param>
        public void SetCellColumnWidth(int columnIndex, int rowIndex, double width)
        {
            Range cellRange = this.worksheet.Cells[rowIndex, columnIndex];
            cellRange.Columns.ColumnWidth = width;
        }

        /// <summary>
        /// 设置单元格的行高
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="height"></param>
        public void SetCellRowHeight(int columnIndex, int rowIndex, double height)
        {
            Range cellRange = this.worksheet.Cells[rowIndex, columnIndex];
            cellRange.Rows.RowHeight = height;
        }

        /// <summary>
        /// 设置单元格的列宽自动填充
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        public void SetColumnAutoWidth(string columnIndex, int rowIndex)
        {
            Range cellRange = this.worksheet.Cells[rowIndex, columnIndex];
            if (cellRange != null)
                cellRange.EntireColumn.AutoFit();
        }

        /// <summary>
        /// 获取单元格范围
        /// </summary>
        /// <param name="beginColumn"></param>
        /// <param name="beginRow"></param>
        /// <param name="endColumn"></param>
        /// <param name="endRow"></param>
        /// <returns></returns>
        public Range GetCellRange(int beginColumn, int beginRow, int endColumn = 0, int endRow = 0)
        {
            if (beginColumn < 1 || beginRow < 1)
                return null;
            if (endColumn < 1)
                endColumn = beginColumn;
            if (endRow < 1)
                endRow = beginRow;
            return this.worksheet.get_Range(this.GetAddress(beginRow, beginColumn), this.GetAddress(endRow, endColumn));
        }
        
        /// <summary>
        /// 设置区域单元格的值，值用逗号分隔，被填充到一片区域中
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="values"></param>
        public void SetRangeValue(int columnIndex, int rowIndex, string[] values)
        {
            Range range = this.GetCellRange(columnIndex, rowIndex, columnIndex + values.Length - 1, rowIndex);
            range.NumberFormatLocal = "@";//设置单元格格式为默认文本
            range.ColumnWidth = 30;
            range.Value2 = values;
        }

        /// <summary>
        /// 设置单元格的值
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="values"></param>
        public void SetValue(Range cellRange, object[] values)
        {
            cellRange.Value = values;
        }

        /// <summary>
        /// 设置边框颜色
        /// </summary>
        /// <param name="range"></param>
        /// <param name="color"></param>
        public void SetBordersColor(Range cellRange, System.Drawing.Color color)
        {
            cellRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(color);
        }

        /// <summary>
        /// 设置边界线型
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="bordersIndex"></param>
        /// <param name="lineStyle"></param>
        public void SetBordersLineStyle(Range cellRange, EnumXlBordersIndex bordersIndex, EnumXlLineStyle lineStyle)
        {
            cellRange.Borders.get_Item((XlBordersIndex)bordersIndex).LineStyle = (XlLineStyle)lineStyle;
        }

        /// <summary>
        /// 设置边界线型
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="lineStyle"></param>
        public void SetBordersLineStyle(Range cellRange, EnumXlLineStyle lineStyle)
        {
            cellRange.Borders.LineStyle = (XlLineStyle)lineStyle;
        }

        /// <summary>
        /// 设置水平对齐方式
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="alignment"></param>
        public void SetHorizontalAlignment(Range cellRange, EnumXlHAlign alignment)
        {
            cellRange.HorizontalAlignment = (XlHAlign)alignment;
        }

        /// <summary>
        /// 设置垂直对齐方式
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="alignment"></param>
        public void SetVerticalAlignment(Range cellRange, EnumXlVAlign alignment)
        {
            cellRange.VerticalAlignment = (XlVAlign)alignment;
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="cellRange"></param>
        public void Merge(Range cellRange)
        {
            cellRange.Merge(false);//若为true，则每一行合并成一个单元格
        }

        /// <summary>
        /// 清除指定单元格的公式
        /// </summary>
        /// <param name="cellRange"></param>
        public void ClearContents(Range cellRange)
        {
            cellRange.ClearContents();
        }

        /// <summary>
        /// 拷贝指定单元格
        /// </summary>
        /// <param name="cellRange"></param>
        public void Copy(Range cellRange)
        {
            cellRange.Copy();
        }

        /// <summary>
        /// 从指定单元格开始粘贴
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        public void Paste(int columnIndex, int rowIndex)
        {
            this.worksheet.Range[this.GetAddress(rowIndex, columnIndex)].PasteSpecial();
        }

        /// <summary>
        /// 插入一行
        /// </summary>
        /// <param name="rowIndex"></param>
        public void InsertRow(int rowIndex)
        {
            this.worksheet.Rows[rowIndex].Insert();
        }

        /// <summary>
        /// 插入一列
        /// </summary>
        /// <param name="columnIndex"></param>
        public void InsertColumn(int columnIndex)
        {
            this.worksheet.Columns[columnIndex].Insert();
        }

        /// <summary>
        /// 删除一行
        /// </summary>
        /// <param name="rowIndex"></param>
        public void DeleteRow(int rowIndex)
        {
            this.worksheet.Rows[rowIndex].Delete();
        }

        /// <summary>
        /// 删除一列
        /// </summary>
        /// <param name="columnIndex"></param>
        public void DeleteColumn(int columnIndex)
        {
            this.worksheet.Columns[columnIndex].Delete();
        }

        /// <summary>
        /// 设置单元格的公式
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="formula"></param>
        public void SetFormula(int columnIndex, int rowIndex, string formula)
        {
            Range cellRange = this.worksheet.Cells[rowIndex, columnIndex];
            cellRange.Formula = formula;
        }

        /// <summary>
        /// 自动换行
        /// </summary>
        /// <param name="range"></param>
        /// <param name="autoWrapText"></param>
        public void AutoWrapText(Range cellRange, bool autoWrapText)
        {
            cellRange.WrapText = autoWrapText;
        }

        /// <summary>
        /// 设置单元格字体颜色
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="color"></param>
        public void SetFontColor(Range cellRange, System.Drawing.Color color)
        {
            cellRange.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
        }

        /// <summary>
        /// 设置单元格的背景色
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="color"></param>
        public void SetBackColor(Range cellRange, System.Drawing.Color color)
        {
            cellRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
        }

        /// <summary>
        /// 设置数值的显示样式
        /// </summary>
        /// <param name="cellRange"></param>
        /// <param name="format">如"#,##0.00"的显示格式</param>
        public void SetNumberFormat(Range cellRange, string format)
        {
            cellRange.NumberFormat = format;
        }

        /// <summary>
        /// 清除样式
        /// </summary>
        /// <param name="cellRange"></param>
        public void ClearFormat(Range cellRange)
        {
            cellRange.ClearFormats();
        }

        /// <summary>
        /// 为单元格添加超级链接
        /// </summary>         
        /// <param name="CurSheet">Worksheet</param>         
        /// <param name="objCell">单元格</param>         
        /// <param name="strAddress">链接地址</param>         
        /// <param name="strTip">屏幕提示</param>         
        /// <param name="strText">链接文本</param>         
        public void AddHyperLink(int columnIndex, int rowIndex, string strAddress, string strTip, string strText)         
        {             
            this.worksheet.Hyperlinks.Add(this.worksheet.Cells[rowIndex,columnIndex],
                strAddress, missing, strTip, strText);
        }

        /// <summary>
        /// 在指定的单元格中插入图片
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="imageFile"></param>
        public void InsertImage(int columnIndex, int rowIndex, string imageFile)
        {            
            Range range = (Range)this.worksheet.Cells[rowIndex, columnIndex];
            this.worksheet.Activate();//防止“类range的select方法无效”错误
            range.Select();
            float picLeft = Convert.ToSingle(range.Left);
            float picTop = Convert.ToSingle(range.Top);
            System.Drawing.Image image = System.Drawing.Image.FromFile(imageFile);
            float picWidth = image.Width;
            float picHeight = image.Height;
            this.worksheet.Shapes.AddPicture(imageFile, Microsoft.Office.Core.MsoTriState.msoFalse,
                 Microsoft.Office.Core.MsoTriState.msoTrue, picLeft,
                 picTop, picWidth, picHeight);
        }

        /// <summary>
        /// 在指定的单元格中插入图片
        /// </summary>
        /// <param name="range"></param>
        /// <param name="imageFile"></param>
        /// <param name="incrementTop">上方偏移</param>
        /// <param name="incrementLeft">左侧偏移</param>
        public void InsertImage(int beginColumn, int beginRow, int endColumn, int endRow,
            string imageFile, float incrementTop = 0f, float incrementLeft = 0f)
        {
            Range range = this.GetCellRange(beginColumn, beginRow, endColumn, endRow);
            this.worksheet.Activate();
            range.Select();
            float picLeft = Convert.ToSingle(range.Left) + incrementLeft;
            float picTop = Convert.ToSingle(range.Top) + incrementTop;
            System.Drawing.Image image = System.Drawing.Image.FromFile(imageFile);
            float picWidth = Convert.ToSingle(range.Width) - 2 * incrementLeft;
            float picHeight = Convert.ToSingle(range.Height) - 2 * incrementTop;
            this.worksheet.Shapes.AddPicture(imageFile, Microsoft.Office.Core.MsoTriState.msoFalse,
                 Microsoft.Office.Core.MsoTriState.msoTrue, picLeft,
                 picTop, picWidth, picHeight);
            image.Dispose();
        }

        /// <summary>
        /// 替换文本
        /// </summary>
        /// <param name="oldStr"></param>
        /// <param name="value"></param>
        public void ReplaceText(string oldStr, string value)
        {
            object matchCase = (object)false;
            this.worksheet.Cells.Replace(oldStr, value, missing, missing, matchCase, missing, missing, missing);
        }

        /// <summary>
        /// 替换图片
        /// </summary>
        /// <param name="oldStr"></param>
        /// <param name="imageFile"></param>
        public void ReplaceImage(string oldStr, string imageFile)
        {
            object matchCase = (object)false;
            Range range = this.worksheet.Cells.Find(oldStr, missing, missing, missing, missing, XlSearchDirection.xlNext, matchCase, missing, missing);
            if (range != null)
            {
                //导出图片
                if (System.IO.File.Exists(imageFile))
                {
                    while (range != null)
                    {
                        //删除替换文本
                        range.Value2 = String.Empty;
                        //添加图片
                        this.worksheet.Activate();
                        range.Select();
                        float picLeft = Convert.ToSingle(range.Left);
                        float picTop = Convert.ToSingle(range.Top);
                        System.Drawing.Image image = System.Drawing.Image.FromFile(imageFile);
                        float picWidth = image.Width;
                        float picHeight = image.Height;
                        this.worksheet.Shapes.AddPicture(imageFile, Microsoft.Office.Core.MsoTriState.msoFalse,
                                Microsoft.Office.Core.MsoTriState.msoTrue, picLeft,
                                picTop, picWidth, picHeight);
                        range = this.worksheet.Cells.Find(oldStr, missing, missing, missing, missing, XlSearchDirection.xlNext, matchCase, missing, missing);
                    }
                }
                else
                {
                    object value = String.Empty;
                    this.worksheet.Cells.Replace(oldStr, value, missing, missing, matchCase, missing, missing, missing);
                    range = null;
                }
            }
        }

        #endregion

        #region 打印设置

        /// <summary>
        /// 打印预览
        /// </summary>
        public void PrintPreview()
        {
            this.application.Visible = true;
            this.worksheet.PrintPreview();
        }

        /// <summary>
        /// 打印输出
        /// </summary>
        public void Print()
        {
            this.worksheet.PrintOutEx(missing, missing, missing, missing, missing, missing, missing, missing, missing);
        }

        /// <summary>
        /// 在指定行前面插入分页符
        /// </summary>
        /// <param name="rowIndex"></param>
        public void InsertRowPageBreak(int rowIndex)
        {
            this.worksheet.Rows[rowIndex].PageBreak = 1;
        }

        /// <summary>
        /// 在指定列前面插入分页符
        /// </summary>
        /// <param name="columnIndex"></param>
        public void InsertColumnPageBreak(int columnIndex)
        {
            this.worksheet.Columns[columnIndex].PageBreak = 1;
        }

        /// <summary>
        /// 删除指定行前面的分页符
        /// </summary>
        /// <param name="rowIndex"></param>
        public void DeleteRowPageBreak(int rowIndex)
        {
            this.worksheet.Rows[rowIndex].PageBreak = 0;
        }

        /// <summary>
        /// 删除指定列前面的分页符
        /// </summary>
        /// <param name="columnIndex"></param>
        public void DeleteColumnPageBreak(int columnIndex)
        {
            this.worksheet.Columns[columnIndex].PageBreak = 0;
        }

        /// <summary>
        /// 设置纸张大小
        /// </summary>
        /// <param name="paperSize"></param>
        public void SetPrintPaperSize(EnumXlPaperSize paperSize)
        {
            this.worksheet.PageSetup.PaperSize = (XlPaperSize)paperSize;    
        }
 
        /// <summary>
        /// 设置顶端标题行，例如"$1:$3"
        /// </summary>
        /// <param name="titleRows"></param>
        public void SetPrintTitleRows(string titleRows)
        {
            this.worksheet.PageSetup.PrintTitleRows = titleRows;
        }

        /// <summary>
        /// 设置页面方向
        /// </summary>
        /// <param name="orientation"></param>
        public void SetPrintOrientation(EnumXlPageOrientation orientation)
        {
            this.worksheet.PageSetup.Orientation = (XlPageOrientation)orientation;
        }
  
        /// <summary>
        /// 设置页边距（单位厘米）
        /// </summary>
        /// <param name="top"></param>
        /// <param name="bottom"></param>
        /// <param name="left"></param>
        /// <param name="right"></param>
        public void SetPrintMargin(double top, double bottom, double left, double right)
        {
            this.worksheet.PageSetup.TopMargin = this.application.CentimetersToPoints(top);
            this.worksheet.PageSetup.BottomMargin = this.application.CentimetersToPoints(bottom);
            this.worksheet.PageSetup.LeftMargin = this.application.CentimetersToPoints(left);
            this.worksheet.PageSetup.RightMargin = this.application.CentimetersToPoints(right);
        }

        /// <summary>
        /// 设置页眉边距
        /// </summary>
        /// <param name="header"></param>
        public void SetPrintHeaderMargin(double header)
        {
            this.worksheet.PageSetup.HeaderMargin = this.application.CentimetersToPoints(header);            
        }

        /// <summary>
        /// 设置页脚边距
        /// </summary>
        /// <param name="footer"></param>
        public void SetPrintFooterMargin(double footer)
        {
            this.worksheet.PageSetup.FooterMargin = this.application.CentimetersToPoints(footer);
        }

        /// <summary>
        /// 设置页面水平居中
        /// </summary>
        /// <param name="centerHorizontally"></param>
        public void SetPrintCenterHorizontally(bool centerHorizontally)
        {
            this.worksheet.PageSetup.CenterHorizontally = centerHorizontally;
        }

        /// <summary>
        /// 设置页面垂直居中
        /// </summary>
        /// <param name="centerHorizontally"></param>
        public void SetPrintCenterVertically(bool centerVertically)
        {
            this.worksheet.PageSetup.CenterVertically = centerVertically;
        }

        /// <summary>
        /// 设置中间页眉内容
        /// </summary>
        /// <param name="header"></param>
        public void SetPrintCenterHeader(string header)
        {
            this.worksheet.PageSetup.CenterHeader = header;
        }
 
        /// <summary>
        /// 设置中间页脚内容（例如："第&P页，共&N页"）
        /// </summary>
        /// <param name="footer"></param>
        public void SetPrintCenterFooter(string footer)
        {
            this.worksheet.PageSetup.CenterFooter = footer;
        }

        /// <summary>
        /// 打印网格线
        /// </summary>
        /// <param name="printGridLines"></param>
        public void SetPrintGridLines(bool printGridLines)
        {
            this.worksheet.PageSetup.PrintGridlines = printGridLines;
        }

        #endregion

        #endregion

        #region 私有方法

        /// <summary>
        /// 打开文档
        /// </summary>
        /// <param name="wordFile"></param>
        /// <returns></returns>
        private Workbook OpenExcel(string excelFile, EnumXlMode mode)
        {
            try
            {
                if (excelFile == null || excelFile.Equals(String.Empty))
                    throw new Exception("excel文件路径不能为空！");

                switch (mode)
                {
                    case EnumXlMode.exCreate:
                        return this.CreateExcel(excelFile);
                    case EnumXlMode.exOpen:
                        return this.OpenExcel(excelFile);
                    case EnumXlMode.exOpenOrCreate:
                        if (File.Exists(excelFile))
                            return this.OpenExcel(excelFile);
                        else
                            return this.CreateExcel(excelFile);
                    default:
                        throw new NotImplementedException();
                }
            }
            catch
            {
                throw new Exception("打开或创建word文档失败！");
            }
        }

        /// <summary>
        /// 打开excel文档
        /// </summary>
        /// <param name="wordFile"></param>
        /// <returns></returns>
        private Workbook OpenExcel(string excelFile)
        {
            object readOnly = false; //只读
            object addToMru = false;  //添加到最近打开的文档

            return this.application.Workbooks.Open(
                excelFile, missing, readOnly, missing, missing, missing,
                missing, missing, missing, missing, missing, missing, addToMru, missing, missing);
        }

        /// <summary>
        /// 新建excel文档
        /// </summary>
        /// <param name="wordFile"></param>
        /// <returns></returns>
        private Workbook CreateExcel(string excelFile)
        {
            Workbook book = this.application.Workbooks.Add(missing);
            object fileName = (object)excelFile;
            book.SaveAs(fileName, missing, missing, missing, missing,
                    missing, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
            return book;
        }

        ///<summary>   
        ///得到单元格的地址，支持 27*26 列
        ///</summary>   
        ///<param name="row">行</param>   
        ///<param name="col">列</param>   
        ///<returns>返回地址</returns>           
        private string GetAddress(int row, int col)
        {
            if (row < 1 || col < 1 || col > 27 * 26)
                throw new OverflowException();

            string addressStr = this.GetColumnIndex(col);
            addressStr += row.ToString();
            return addressStr;
        }

        /// <summary>
        /// 根据索引（大于1的整数）获取excel中对应的列标识（A,B,...Z,AA,...ZZ)
        /// </summary>
        /// <param name="index">索引，大于1的整数</param>
        /// <returns>excel中对应的列标识</returns>
        private string GetColumnIndex(int index)
        {
            if (index <= 0)
            {
                return String.Empty;
            }
            
            string addressStr = String.Empty;
            index = index - 1;
            int num = index / 26;

            if (num > 0)
            {
                addressStr = Convert.ToChar(num - 1 + 65).ToString();
            }

            char addressChr = Convert.ToChar(index % 26 + 65);

            addressStr = addressStr + addressChr;
            return addressStr;
        }

        #endregion

        #region IDisposable成员

        /// <summary>
        /// 获取进程ID
        /// </summary>
        /// <param name="hwnd">窗口句柄 由FindWindow获取</param>
        /// <param name="ID">进程ID</param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

        /// <summary>
        /// 关闭Excel进程
        /// </summary>
        /// <param name="excel"></param>
        public void KillExcel(Microsoft.Office.Interop.Excel._Application excel)
        {
            //获得句柄
            IntPtr ptr = new IntPtr(excel.Hwnd);
            //获取进程ID
            int processID = 0;
            GetWindowThreadProcessId(ptr, out processID);
            //获取进程
            Process process = Process.GetProcessById(processID);
            //关闭进程
            if (!process.CloseMainWindow())//尝试关闭进程
            {
                process.Kill();//强制关闭
            }
        }

        public void Dispose()
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(this.worksheet);
            this.worksheet = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(this.workbook);
            this.workbook = null;
            if (this.application != null)
            {
                this.application.Quit();

                //强制关闭Excel进程（解决程序异常关闭的情况下，再次导出异常的问题）
                this.KillExcel(this.application);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(this.application);
                this.application = null;
            }
        }

        #endregion
    }
}
